use crate::{error::SqlxErr, utils::current_timestamp};
use serde::Serialize;
use sqlx::Row;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_order (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    client_create_timestamp INTEGER NOT NULL, 
    server_create_timestamp INTEGER NOT NULL, 
    product_id INTEGER NOT NULL, 
    device_id INTEGER NOT NULL, 
    replenish_id INTEGER NOT NULL, 
    col INTEGER NOT NULL,
    row INTEGER NOT NULL, 
    pay_method TEXT NOT NULL, 
    status INTEGER NOT NULL,
    remark TEXT NOT NULL, 
    UNIQUE (device_id, client_create_timestamp),
    FOREIGN KEY (device_id) REFERENCES tb_device(id) ON DELETE CASCADE
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct OrderInfo {
    client_create_timestamp: i64,
    product_id: i64,
    replenish_id: i64,
    col: u8,
    row: u8,
    pay_method: String,
    status: i32,
    remark: String,
}

pub async fn create(device_id: i64, info: &OrderInfo) -> Result<i64, SqlxErr> {
    let mut tx = get_pool().begin().await?;

    let id = match is_exists(&mut tx, device_id, info.client_create_timestamp).await? {
        Some(id) => id,
        None => create_impl(&mut tx, device_id, info).await?,
    };
    tx.commit().await?;

    Ok(id)
}

async fn is_exists(
    tx: &mut SqliteConnection,
    device_id: i64,
    client_create_timestamp: i64,
) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(
        r#"SELECT id FROM tb_order 
        WHERE device_id = ? AND client_create_timestamp = ? LIMIT 1"#,
    )
    .bind(device_id)
    .bind(client_create_timestamp)
    .fetch_optional(tx)
    .await?;
    Ok(row.and_then(|r| r.get(0)))
}

async fn create_impl(
    tx: &mut SqliteConnection,
    device_id: i64,
    info: &OrderInfo,
) -> Result<i64, SqlxErr> {
    let server_timestamp = current_timestamp() as i64;

    let ret = sqlx::query(
        r#"
        INSERT INTO tb_order (client_timestamp, server_create_timestamp, 
        product_id, device_id, replenish_id, 
        col, row, pay_method, status, remark) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    "#,
    )
    .bind(info.client_create_timestamp)
    .bind(server_timestamp)
    .bind(info.product_id)
    .bind(device_id)
    .bind(info.replenish_id)
    .bind(info.col)
    .bind(info.row)
    .bind(&info.pay_method)
    .bind(info.status)
    .bind(&info.remark)
    .execute(tx)
    .await?;

    Ok(ret.last_insert_rowid())
}
